import logging
import math
from idlelib.query import Query
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from fastapi.security import oauth2
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.engine import create
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

my_teacher_router = APIRouter(prefix="/school/tsTeacher", tags=["教室管理"], dependencies=[Depends(oauth2_scheme)])


class TeacherQuerySearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    fteacherno: Optional[str] = Field("", description="教师编号")
    fteachername: Optional[str] = Field("", description="教师姓名(支持前缀匹配)")


@my_teacher_router.get("/list", summary="老师查询接口")
def list_my_teacher(search: TeacherQuerySearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from a_ts_teacher where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fteacherno != "":
        sql = sql + " and fteacher_no = :fteacherno "
        param["fteacherno"] = search.fteacherno
    if search.fteachername != "":
        sql = sql + " and fteacher_name like concat('%', :fteachername ,'%')"
        param["fteachername"] = search.fteachername
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fteacher_no,fteacher_name,fsite_no,fwork_no,fuserno,"
    sql = sql + " fnote,flayer_state,fupddate,foperator"
    sql = sql + " from a_ts_teacher where 1=1"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fteacherno != "":
        sql = sql + " and fteacher_no = :fteacherno "
        param["fteacherno"] = search.fteacherno
    if search.fteachername != "":
        sql = sql + " and fteacher_name like concat('%', :fteachername ,'%')"
        param["fteachername"] = search.fteachername
    if search.order == "desc":
        sql = sql + " order by  fteacher_no desc "
    else:
        sql = sql + " order by  fteacher_no "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fteacher_no,row_number() OVER (PARTITION BY b.fteacher_no ORDER BY b.fclass_no DESC) fentryid"
    sql_3 = sql_3 + ",b.fclass_no,c.fclass_name"
    sql_3 = sql_3 + " from a_ts_teacher a,a_ts_teacher_class b,a_ts_class c "
    sql_3 = sql_3 + " where a.fteacher_no=b.fteacher_no "
    sql_3 = sql_3 + " and b.fclass_no=c.fclass_no"
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = search.fsiteNo
    if search.fteacherno != "":
        sql_3 = sql_3 + " and a.fteacher_no = :fteacherno "
        param["fteacherno"] = search.fteacherno
    if search.fteachername != "":
        sql_3 = sql_3 + " and a.fteacher_name like concat('%', :fteachername ,'%')"
        param["fteachername"] = search.fteachername
    sql_3 = sql_3 + " order by b.fteacher_no,b.fclass_no"
    data_detail1 = db.execute(text(sql_3), param).fetchall()

    #  课程 明细列表
    sql_2 = " select b.fteacher_no,row_number() OVER (PARTITION BY b.fteacher_no ORDER BY b.fcourse_no DESC) fentryid"
    sql_2 = sql_2 + ",b.fcourse_no,c.fcourse_name"
    sql_2 = sql_2 + " from a_ts_teacher a,a_ts_teacher_course b,a_ts_course c "
    sql_2 = sql_2 + " where a.fteacher_no=b.fteacher_no "
    sql_2 = sql_2 + " and b.fcourse_no=c.fcourse_no"
    if search.fsiteNo != "":
        sql_2 = sql_2 + " and a.fsite_no= :fsiteNo "
    if search.fteacherno != "":
        sql_2 = sql_2 + " and a.fteacher_no = :fteacherno "
    if search.fteachername != "":
        sql_2 = sql_2 + " and a.fteacher_name like concat('%', :fteachername ,'%')"
        param["fteachername"] = search.fteachername
    sql_2 = sql_2 + " order by b.fteacher_no,b.fcourse_no"
    data_detail2 = db.execute(text(sql_2), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fteacherno'] = data[key][0]
        d1['fteachername'] = data[key][1]
        d1['fsiteNo'] = data[key][2]
        d1['fworkno'] = data[key][3]
        d1['fuserno'] = data[key][4]
        d1['fnote'] = data[key][5]
        d1['flayerstate'] = data[key][6]
        d1['fupddate'] = data[key][7]
        d1['foperator'] = data[key][8]
        sbillno = data[key][0]

        detail_list1 = []
        for j1 in range(len(data_detail1)):
            if (str(data_detail1[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fteacherno'] = data_detail1[j1][0]
                d2['fentryid'] = data_detail1[j1][1]
                d2['fclassno'] = data_detail1[j1][2]
                d2['fclassname'] = data_detail1[j1][3]
                detail_list1.append(d2)

        detail_list2 = []
        for j2 in range(len(data_detail2)):
            if (str(data_detail2[j2][0]) == str(sbillno)):
                d3 = {}
                d3['fteacherno'] = data_detail2[j2][0]
                d3['fentryid'] = data_detail2[j2][1]
                d3['fcourseno'] = data_detail2[j2][2]
                d3['fcoursename'] = data_detail2[j2][3]
                detail_list2.append(d3)

        d1["atsList1"] = detail_list1
        d1["atsList2"] = detail_list2
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class TeacherCourse(BaseModel):
    """教师授课课程"""
    fcourseno: str = Field(..., description="课程编号")


class TeacherClass(BaseModel):
    """教师授课班级"""
    fclassno: str = Field(..., description="班级编号")


class TeacherCreate(BaseModel):
    """老师资料新增请求参数"""
    fteacherno: str = Field(..., description="教师编号")
    fteachername: str = Field(..., description="教师姓名")
    fsiteno: str = Field(..., description="站点编号")
    flayerstate: int = Field(..., description="教师状态")
    fupddate: str = Field(..., description="更新日期(格式需与数据库存储一致)")
    foperator: str = Field(..., description="操作人ID")
    fworkno: Optional[str] = Field("", description="工作编号，默认为空")
    fuserno: Optional[str] = Field("0", description="用户编号，默认为0")
    fnote: Optional[str] = Field("", description="备注信息，默认为空")
    atsList1: List[TeacherClass] = Field([], description="授课班级列表，默认为空")
    atsList2: List[TeacherCourse] = Field([], description="授课课程列表，默认为空")


@my_teacher_router.post("/create", summary="老师资料新增接口")
def create_my_teacher(create: TeacherCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fteacherno = create.fteacherno
        with db.begin():
            sql = "select 1 from a_ts_teacher where fteacher_no= :fteacherno "
            data = db.execute(text(sql), {
                "fteacherno": create.fteacherno,
            }).fetchall()

            if len(data) > 0:
                return BaseResponse.error(message="该编号已存在")
            else:
                d1 = {}
                sql = "insert into a_ts_teacher(fteacher_no,fteacher_name,fsite_no,fwork_no,fuserno,fnote,flayer_state,fupddate,foperator)"
                sql = sql + " values( :fteacherno , :fteachername , :fsiteno , :fworkno , :fuserno , :fnote , :flayerstate ,now(), :userid );"
                db.execute(text(sql), {
                    "fteacherno": fteacherno,
                    "fteachername": create.fteachername,
                    "fsiteno": create.fsiteno,
                    "fworkno": create.fworkno,
                    "fuserno": create.fuserno,
                    "fnote": create.fnote,
                    "flayerstate": create.flayerstate,
                    "userid": user_id,
                })
                # 课程列表
                atsList2 = create.atsList2
                for ats in atsList2:
                    sql = "insert into a_ts_teacher_course(fteacher_no,fcourse_no)"
                    sql = sql + " values( :fteacherno , :fcourseno );"
                    db.execute(text(sql), {
                        "fteacherno": fteacherno,
                        "fcourseno": ats.fcourseno,
                    })
                # 教室列表
                atsList1 = create.atsList1
                for ats in atsList1:
                    sql = "insert into a_ts_teacher_class(fteacher_no,fclass_no)"
                    sql = sql + " values( :fteacherno , :fclassno );"
                    db.execute(text(sql), {
                        "fteacherno": fteacherno,
                        "fcourseno": ats.fclassno,
                    })
                d1['fteacherno'] = fteacherno
                d1['fteachername'] = create.fteachername
                d1['fsiteNo'] = create.fsiteno
                d1['fworkno'] = create.fworkno
                d1['fuserno'] = create.fuserno
                d1['fnote'] = create.fnote
                d1['flayerstate'] = create.flayerstate
                d1['fupddate'] = create.fupddate
                d1['foperator'] = create.foperator
                d1["atsList1"] = atsList1
                d1["atsList2"] = atsList2
                return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@my_teacher_router.post("/update", summary="教师修改接口")
def update_my_teacher(update: TeacherCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        fteacherno = update.fteacherno
        with db.begin():
            sql = "update a_ts_teacher"
            sql = sql + " set fteacher_name= :fteachername ,fsite_no= :fsiteno "
            sql = sql + ",fwork_no= :fworkno ,fnote= :fnote ,fuserno= :fuserno "
            sql = sql + ",flayer_state= :flayerstate ,fupddate=now(),foperator= :userid "
            sql = sql + " where fteacher_no= :fteacherno "
            db.execute(text(sql), {
                "fteachername": update.fteachername,
                "fsiteno": update.fsiteno,
                "fworkno": update.fworkno,
                "fnote": update.fnote,
                "fuserno": update.fuserno,
                "flayerstate": update.flayerstate,
                "userid": user_id,
                "fteacherno": fteacherno,
            })

            # 人员列表
            sql = "delete from a_ts_teacher_course where fteacher_no= :fteacherno "
            db.execute(text(sql), {
                "fteacherno": fteacherno,
            })

            # 课程列表
            atsList2 = update.atsList2
            for ats in atsList2:
                sql = "insert into a_ts_teacher_course(fteacher_no,fcourse_no)"
                sql = sql + " values( :fteacherno , :fcourseno );"
                db.execute(text(sql), {
                    "fteacherno": fteacherno,
                    "fcourseno": ats.fcourseno,
                })
            # 教室列表
            atsList1 = update.atsList1
            for ats in atsList1:
                sql = "insert into a_ts_teacher_class(fteacher_no,fclass_no)"
                sql = sql + " values( :fteacherno , :fclassno );"
                db.execute(text(sql), {
                    "fteacherno": fteacherno,
                    "fcourseno": ats.fclassno,
                })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@my_teacher_router.post("/delete/{fteacherno}", summary="教师删除接口")
def delete_my_teacher(fteacherno: str = Path(description="教师编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from a_ts_teacher where fteacher_no= :fteacherno "
            db.execute(text(sql), {
                "fteacherno": fteacherno,
            })
            sql = "delete from a_ts_teacher_class where fteacher_no= :fteacherno "
            db.execute(text(sql), {
                "fteacherno": fteacherno,
            })
            sql = "delete from a_ts_teacher_course where fteacher_no= :fteacherno "
            db.execute(text(sql), {
                "fteacherno": fteacherno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
